package com.quotes.io;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map.Entry;
import java.util.Properties;
import org.apache.log4j.Logger;
import com.quotes.datamodell.Quotes;
import com.quotes.datamodell.QuotesList;
import com.quotes.datamodell.QuotesType;
import com.quotes.datamodell.Symbol;
import com.quotes.datamodell.SymbolList;
import com.quotes.datamodell.TimeSeries;

public class DerbyAdapter
{
	private Logger logger = Logger.getLogger(getClass().toString());
    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private String protocol = "jdbc:derby:";
    private String dbName = "quotes";
    private Connection connection = null;

    public DerbyAdapter() throws Exception {
    	loadDriver();
    	connect();
    	createStructure();
    }
    
    private void loadDriver() throws Exception {
        Class.forName(driver).newInstance();
        logger.debug("Loaded the appropriate driver");
    }
    
    private void connect() throws Exception {
        Properties props = new Properties();
        this.connection = DriverManager.getConnection(protocol + dbName + ";create=true", props);
        this.connection.setAutoCommit(false);

        logger.debug("Connected to database " + dbName);
    }
    
    private java.util.List<String> getTableNames() throws Exception {
    	DatabaseMetaData metaData = this.connection.getMetaData();
    	ResultSet result = metaData.getTables(null, null, null, null);
    	java.util.List<String> tableNames = new ArrayList<String>();
    	
    	while (result.next()) {
    		tableNames.add(result.getString("TABLE_NAME"));
    	}
    	
    	return tableNames;
    }
       
    private void createStructure() throws Exception {
    	java.util.List<String> tableNames = this.getTableNames();
    	Statement statement = this.connection.createStatement();
	    
    	if (!tableNames.contains("SYMBOLS")) {
	    	statement.execute("create table symbols(" +
	    			"id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
	    			"yahoo_name VARCHAR(10) NOT NULL," +
	    			"full_name varchar(100))");

	    	logger.debug("Table SYMBOLS created!");
    	}
	   
    	if (!tableNames.contains("QUOTES_TYPE")) {
	    	statement.execute("create table quotes_type(" +
	    			"id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
	    			"type VARCHAR(10) NOT NULL)");
	    	
	    	statement.execute("INSERT INTO quotes_type (type) values ('OPEN')");
	    	statement.execute("INSERT INTO quotes_type (type) values ('CLOSE')");
	    	statement.execute("INSERT INTO quotes_type (type) values ('HIGH')");
	    	statement.execute("INSERT INTO quotes_type (type) values ('LOW')");
	    	statement.execute("INSERT INTO quotes_type (type) values ('VOLUME')");
	    	statement.execute("INSERT INTO quotes_type (type) values ('ADJCLOSE')");
	    	
	    	logger.debug("Table QUOTES_TYPE created!");
    	}
	    
    	if (!tableNames.contains("QUOTES")) {
	    	statement.execute("create table quotes(" +
	    			"symbol_id INTEGER NOT NULL," +
	    			"quotes_type_id INTEGER NOT NULL," +
	    			"date TIMESTAMP NOT NULL," +
	    			"value DOUBLE NOT NULL)");
	    	
	    	statement.execute("create index idx_quotes on quotes(" +
	    			"symbol_id,quotes_type_id)");
	    	
	    	logger.debug("Table QUOTES created!");
    	}
    	
    	this.connection.commit();
    }
    
    public SymbolList getSymbols() throws Exception {
    	SymbolList result = new SymbolList();
    	
    	Statement statement = this.connection.createStatement();
    	ResultSet sqlResult = statement.executeQuery("select * from symbols");
    	
    	while (sqlResult.next()) {
    		Symbol symbol = new Symbol();
    		symbol.setId(sqlResult.getInt("id"));
    		symbol.setYahooName(sqlResult.getString("yahoo_name"));
    		symbol.setFullName(sqlResult.getString("full_name"));
    		result.add(symbol);
    	}
    	
    	sqlResult.close();
    	statement.close();
    	
    	logger.debug("List of symbols loaded from DB!");
    	return result;
    }
    
    private void insertSymbolToDb(Symbol symbol) throws Exception {
    	PreparedStatement sta = this.connection.prepareStatement(
    			"INSERT INTO SYMBOLS (yahoo_name, full_name) VALUES (?, ?)", new String[]{"ID"});
    	
		sta.setString(1, symbol.getYahooName());
		sta.setString(2, symbol.getFullName());
		sta.executeUpdate();
		
		ResultSet set = sta.getGeneratedKeys();
		if (set.next()) {
			int id = set.getInt(1);
			symbol.setId(id);
		}

		set.close();
    	sta.close();
    }
    
    private void updateSymbolInDb(Symbol symbol) throws Exception {
    	PreparedStatement sta = this.connection.prepareStatement(
    			"UPDATE SYMBOLS set yahoo_name=?, full_name=? where id=?");
    	
		sta.setString(1, symbol.getYahooName());
		sta.setString(2, symbol.getFullName());
		sta.setInt(3, symbol.getId());
		sta.executeUpdate();
    	sta.close();
    }
    
    public void setSymbols(SymbolList symbols) throws Exception {
    	for (Symbol symbol : symbols.getSymbols()) {
    		if (symbol.getId() == 0) {
    			insertSymbolToDb(symbol);
    		} else {
    			updateSymbolInDb(symbol);
    		}
    	}

    	this.connection.commit();
    	logger.debug("List of symbols written to DB!");
    }
    
    private TimeSeries loadTimeSeries(int symbolId, QuotesType quotesType) throws Exception {
    	TimeSeries result = new TimeSeries();
    	
    	PreparedStatement sta = this.connection.prepareStatement(
    			"select * from quotes where symbol_id=? and quotes_type_id=?");
    	sta.setInt(1, symbolId);
    	sta.setInt(2, quotesType.getValue());
    	ResultSet sqlResult = sta.executeQuery();
    	
    	while (sqlResult.next()) {
    		result.put(sqlResult.getDate("DATE"), sqlResult.getDouble("VALUE"));
    	}
    	
    	sqlResult.close();
    	sta.close();
    	
    	return result;
    }
    
    public Quotes getQuotes(Symbol symbol) throws Exception {
    	Quotes quotes = new Quotes(symbol);

    	for (QuotesType quotesType : QuotesType.values()) {
	    	quotes.setQuote(quotesType, loadTimeSeries(symbol.getId(), quotesType));
    	}
    	
    	logger.debug("Quotes for symbol " + symbol.getYahooName() + " loaded from DB!");
    	return quotes;
    }
      
    public QuotesList getQuotes(SymbolList symbols) throws Exception {
    	QuotesList result = new QuotesList();
    	
    	for (Symbol symbol : symbols.getSymbols()) {
    		Quotes quotes =  getQuotes(symbol);
    		result.add(quotes);
    	}
    	
    	return result;
    }
    
    private void deleteTimeSeries(int symbolId, QuotesType quotesType) throws Exception {
    	Statement statement = this.connection.createStatement();
    	statement.execute(
    			"delete from quotes where symbol_id=" + symbolId + " and quotes_type_id=" + quotesType.getValue());
    	
    	statement.close();
    }
    
    private void writeTimeSeries(TimeSeries series, int symbolId, QuotesType quotesType) throws Exception {
    	PreparedStatement sta = this.connection.prepareStatement(
    			"INSERT INTO quotes (symbol_id, quotes_type_id, date, value) VALUES (?, ?, ?, ?)");
    	
    	for (Entry<Date, Double> entry : series.getEntries()) {
    		sta.setInt(1, symbolId);
    		sta.setInt(2, quotesType.getValue());
    		sta.setTimestamp(3, new Timestamp(entry.getKey().getTime()));
    		sta.setDouble(4, entry.getValue());
    		sta.addBatch();
    	}
    	
    	sta.executeBatch();
    	sta.close();
    }
    
    private void saveTimeSeries(TimeSeries series, int symbolId, QuotesType quotesType) throws Exception {
    	this.deleteTimeSeries(symbolId, quotesType);
    	this.writeTimeSeries(series, symbolId, quotesType);
    	this.connection.commit();
    }
    
    public void setQuotes(Quotes quotes) throws Exception {
    	for (QuotesType quotesType : QuotesType.values()) {
    		saveTimeSeries(quotes.getQuote(quotesType), quotes.getSymbol().getId(), quotesType);
    	}
    	
    	logger.debug("Quotes for symbol " + quotes.getSymbol().getYahooName() + " saved to DB!");
    }
    
    public void setQuotes(QuotesList quotesList) throws Exception {
    	for (Quotes quotes : quotesList.getQuotes()) {
    		setQuotes(quotes);
    	}
    }
}
